package database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import database.pojo.PlayerHistorySongInfo;
import database.pojo.UserInfo;

public class PlayerHistorySongInfoDao{
	public PlayerHistorySongInfo insert(PlayerHistorySongInfo pInfo, Connection conn ) throws SQLException {
		
		String sqlInsert = "insert into PlayerHistorySong(openId, songId, maxScore,rank) values( ? , ? , ? ,?)";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sqlInsert);//相当于python操作数据库的游标
			pstmt.setString(1, pInfo.getOpenId());
			pstmt.setString(2, pInfo.getSongId());
			pstmt.setInt(3, pInfo.getMaxScore());
			pstmt.setInt(4, pInfo.getRank());
			pstmt.executeUpdate();
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return pInfo;
		
	}
	
	public boolean isExist(String openId,String songId, Connection conn) throws SQLException{
		String sqlSelect = "select * from PlayerHistorySong where openId = ? and songId = ?  ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1,openId);
			pstmt.setString(2,songId);
			rs = pstmt.executeQuery();
			if(rs.next())

				if(rs.getString("openId") != null)
					return true;
				else {
					return false;
				}
			else {
				 return false;
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
	}
	
	public PlayerHistorySongInfo update(PlayerHistorySongInfo pInfo , Connection conn) throws SQLException {
		
		String sqlUpdate = "update PlayerHistorySong set maxScore = ?,rank = ? where openId = ? and songId = ?";
		PreparedStatement pstmt = null;
		
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, pInfo.getMaxScore());
			pstmt.setInt(2, pInfo.getRank());
			pstmt.setString(3, pInfo.getOpenId());
			pstmt.setString(4, pInfo.getSongId());
			pstmt.executeUpdate();
			
		}catch (Exception e) {
			System.out.println(" update Exception" + e);
		} 
		finally {
			if(pstmt != null) {
				pstmt.close();
			}
		}
		return pInfo;
		
	}
	
	public ArrayList<PlayerHistorySongInfo> selectAllByOpenId(String openId,Connection conn) throws SQLException {
		String sqlSelect = "select * from PlayerHistorySong where openId = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<PlayerHistorySongInfo> pinfoList = new ArrayList<PlayerHistorySongInfo>();
		PlayerHistorySongInfo pInfo = null;
		System.out.println("get open id " + openId);
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				//System.out.println("selectAllByopenId-------songId--"+rs.getString("songId"));
				pInfo =new PlayerHistorySongInfo();
				pInfo.setOpenId(rs.getString("openId"));
				pInfo.setSongId(rs.getString("songId"));
				pInfo.setMaxScore(rs.getInt("maxScore"));
				pInfo.setRank(rs.getInt("rank"));
				pinfoList.add(pInfo);
				}
			}finally {
				if(pstmt != null){
					pstmt.close();
				}
			}
		
		return pinfoList;
	}
	
	public int selectMaxScoreByOpenIdAndSongId(String openId,String songId,Connection conn) throws SQLException {
		String sqlSelect = "select maxScore from PlayerHistorySong where openId = ? and songId";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<PlayerHistorySongInfo> pinfoList = new ArrayList<PlayerHistorySongInfo>();
		PlayerHistorySongInfo pInfo = null;
		System.out.println("get open id " + openId);
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			pstmt.setString(2, songId);
			rs = pstmt.executeQuery();
			if(rs.next()){
				System.out.println("selectAllByopenId-------maxScore--"+rs.getInt("maxScore"));
				}
			}finally {
				if(pstmt != null){
					pstmt.close();
				}
			}
		
		return rs.getInt("maxScore");
	}
}